﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.ComponentModel;
using System.Collections;

[DataObject(true)]
public class StudentReport
{

    private const string CONNECTION_STRING_NAME = "ApplicationConnectionString";

    private const string GET_ALL_STUDENT_ACTIVITY = "SELECT u.user_id, u.username, u.first_name, u.last_name, u.graduation_year, "
                                              + "(SELECT count(action_performed_user) "
                                              + "FROM lgd_user_action "
                                              + "WHERE  action_performed_user = u.user_id "
                                              + "AND action_id = 'S' "
                                              + "AND action_datetime BETWEEN @minDate AND @maxDate) as 'num_sessions', "
                                              + "(SELECT SUM(session_time_minutes) "
                                              + "FROM lgd_user_action "
                                              + "WHERE action_performed_user = u.user_id "
                                              + "AND action_id = 'S' "
                                              + "AND action_datetime BETWEEN @minDate AND @maxDate) as 'total_session_time' "
                                              + "FROM users u "
                                              + "WHERE u.graduation_year > 0 "
                                              + "AND (SELECT count(action_performed_user) "
                                              + "   FROM lgd_user_action "
                                              + "   WHERE  action_performed_user = u.user_id "
                                              + "   AND action_id = 'S' "
                                              + "   AND action_datetime BETWEEN @minDate AND @maxDate) > 0 "
                                              + "ORDER BY num_sessions DESC, total_session_time DESC";

    private const string GET_STUDENT_YEAR_ACTIVITY = "SELECT u.user_id, u.username, u.first_name, u.last_name, u.graduation_year, "
                                                   + "(SELECT count(action_performed_user) "
                                                   + "FROM lgd_user_action "
                                                   + "WHERE  action_performed_user = u.user_id "
                                                   + "AND action_id = 'S' "
                                                   + "AND action_datetime BETWEEN @minDate AND @maxDate) as 'num_sessions', "
                                                   + "(SELECT SUM(session_time_minutes) "
                                                   + "FROM lgd_user_action "
                                                   + "WHERE action_performed_user = u.user_id "
                                                   + "AND action_datetime BETWEEN @minDate AND @maxDate) as 'total_session_time' "
                                                   + "FROM users u "
                                                  + "WHERE u.graduation_year = ?gradYear "
                                                  + "AND (SELECT count(action_performed_user) "
                                                  + "   FROM lgd_user_action "
                                                  + "   WHERE  action_performed_user = u.user_id "
                                                  + "   AND action_id = 'S' "
                                                  + "   AND action_datetime BETWEEN @minDate AND @maxDate) > 0 "
                                                   + "ORDER BY num_sessions DESC, total_session_time DESC";


    private const string GET_STUDENT_MENTOR_ACTIVITY = "SELECT distinct m.mentor_id AS 'id', m.first_name  AS 'first name', m.last_name AS 'last name'"
                                                     + "FROM lgd_mentor_student_actions l, mentor m "
                                                     + "WHERE l.student_id = ?userId "
                                                     + "AND l.action_performed_id = ?actionType "
                                                     + "AND l.date_time BETWEEN @minDate AND @maxDate "
                                                     + "AND m.mentor_id = l.mentor_id";

    private const string GET_STUDENT_PATHWAY_ACTIVITY = "SELECT distinct p.pathway_id AS 'id', p.pathway_name AS 'pathway'"
                                                      + "FROM lkup_focus_area f, lkup_pathway p, lgd_mentor_student_actions l, mentor m "
                                                      + "WHERE l.student_id = ?userId "
                                                      + "AND l.action_performed_id = ?actionType "
                                                      + "AND l.date_time BETWEEN @minDate AND @maxDate "
                                                      + "AND m.mentor_id = l.mentor_id "
                                                      + "AND p.pathway_id = m.pathway_id";


    private const string GET_STUDENT_FOCUS_ACTIVITY = "SELECT distinct f.focus_id AS 'id', f.focus_name AS 'focus'"
                                                      + "FROM lkup_focus_area f, lkup_pathway p, lgd_mentor_student_actions l, mentor m "
                                                      + "WHERE l.student_id = ?userId "
                                                      + "AND l.action_performed_id = ?actionType "
                                                      + "AND l.date_time BETWEEN @minDate AND @maxDate "
                                                      + "AND m.mentor_id = l.mentor_id "
                                                      + "AND p.pathway_id = m.pathway_id "
                                                     + "AND f.focus_id = p.focus_id";
    public string getStudentFocusActivitySQL()
    {
        return GET_STUDENT_FOCUS_ACTIVITY;
    }
    public string getStudentPathwayActivitySQL()
    {
        return GET_STUDENT_PATHWAY_ACTIVITY;
    }
    public string getStudentMentorActivitySQL()
    {
        return GET_STUDENT_MENTOR_ACTIVITY;
    }
    public string getAllStudentActivitySQL()
    {
        return GET_ALL_STUDENT_ACTIVITY;
    }

    public string getStudentYearActivitySQL()
    {
        return GET_STUDENT_YEAR_ACTIVITY;
    }
    private string getConnectionString()
    {
        return ConfigurationManager.ConnectionStrings[CONNECTION_STRING_NAME].ConnectionString;
    }
}
